package com.base.controller;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.afterturn.easypoi.excel.entity.result.ExcelImportResult;
import com.base.model.*;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.util.CollectionUtils;
import org.springframework.util.StringUtils;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

import com.base.service.ExcelService;

import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * excel 导入导出
 * @ClassName: ExcelController
 * @Description:
 * @Author xuyou
 * @DateTime 2019年9月29日 下午1:38:33
 */
@RestController
@RequestMapping("/excel")
public class ExcelController {

	@Autowired
	private ExcelService excelService;

	@RequestMapping("/custom/import")
	public Object customAllotImport(
			@RequestParam("file") MultipartFile file) {
		excelService.customAllotImport(file);
		return "成功";
	}

	/**
	 * 自定义分配模板导出
	 *
	 * @return
	 * @Title: customAllotExport
	 * @Description:
	 * @Author xuyou
	 * @DateTime 2019年9月30日 上午10:38:48
	 */
	@GetMapping("/custom/export")
	public void customAllotExport(HttpServletResponse response) throws Exception {
		excelService.customAllotExport(response, new CustomData());
	}


	/**
	 * 自定义分配模板导出
	 *
	 * @return
	 * @Title: customAllotExport
	 * @Description:
	 * @Author xuyou
	 * @DateTime 2019年9月30日 上午10:38:48
	 */
	@GetMapping("/test")
	public void test(HttpServletRequest request, HttpServletResponse response) throws IOException {
		excelService.test(request, response);
	}


	/**
	 * 多sheet导入Excel数据
	 */
	@PostMapping("multImports")
	public String multImports(MultipartFile file) throws IOException {
		if (null == file) {
			return "文件上传失败";
		}
		List<List<String>> importHeadList = getImportHeadValue(file);
		if (CollectionUtils.isEmpty(importHeadList)) {
			return "解析excel表头出现错误";
		}
		List<List<CustomerExcelModel>> importBodyList = getImportBodyList(file);
		if (CollectionUtils.isEmpty(importBodyList)) {
			return "解析excel内容出现错误";
		}
		ImportReportExcelVM vm = getImportReportExcelVM(importHeadList, importBodyList);
		System.out.println(vm);
		return "文件读取失败";
	}


	/**
	 * 获取Excel中的sheet
	 *
	 * @param file
	 * @return
	 * @throws IOException
	 */
	public static Workbook getWorkBook(MultipartFile file) throws IOException {
		//这样写excel能兼容03和07
		InputStream is = file.getInputStream();
		Workbook hssfWorkbook = null;
		try {
			hssfWorkbook = new HSSFWorkbook(is);
		} catch (Exception ex) {
			is = file.getInputStream();
			hssfWorkbook = new XSSFWorkbook(is);
		}
		return hssfWorkbook;
	}


	public static Object importExcel(MultipartFile file) {
		if (file.isEmpty()) {
			return null;
		}
		//获取excel对象
		Workbook workbook = null;
		try {
			//获取excel
			workbook = new XSSFWorkbook(file.getInputStream());
			//获取工作簿
			int sheetNum = workbook.getNumberOfSheets();
			for (int i = 0; i < sheetNum; i++) {
				Sheet sheetAt = workbook.getSheetAt(i);
				Row row1 = sheetAt.getRow(0);
				for (Cell cell : row1) {
					System.out.print(getCellValue(cell) + "\t");
				}
				System.out.println();
			}
		} catch (IOException e) {
			throw new RuntimeException(e);
		}
		return null;
	}


	private static String getCellValue(Cell cell) {
		int cellType = cell.getCellType();
		if (cellType == 0) {
			return String.valueOf(cell.getNumericCellValue());
		} else {
			return cell.getStringCellValue();
		}
	}

	public static List<List<String>> getImportHeadValue(MultipartFile file) {
		if (file.isEmpty()) {
			return null;
		}
		//获取excel对象
		Workbook workbook = null;
		List<List<String>> headValueList = new ArrayList<>();
		try {
			//获取excel
			workbook = new XSSFWorkbook(file.getInputStream());
			//获取工作簿
			int sheetNum = workbook.getNumberOfSheets();
			for (int i = 0; i < sheetNum; i++) {
				List<String> headValue = new ArrayList<>();
				Sheet sheetAt = workbook.getSheetAt(i);
				Row row1 = sheetAt.getRow(0);
				for (Cell cell : row1) {
					headValue.add(getCellValue(cell));
				}
				headValueList.add(headValue);
			}
		} catch (IOException e) {
			throw new RuntimeException(e);
		}
		return headValueList;
	}

	private List<List<CustomerExcelModel>> getImportBodyList(MultipartFile file) {
		List<List<CustomerExcelModel>> bodyList = new ArrayList<>();
		try {
			//根据file得到Workbook,主要是要根据这个对象获取,传过来的excel有几个sheet页
			Workbook hssfWorkbook = getWorkBook(file);
			//获取sheet数量
			int sheetNum = hssfWorkbook.getNumberOfSheets();
			ImportParams params = new ImportParams();
			//表头在第几行
			params.setTitleRows(0);
			for (int sheetIndex = 0; sheetIndex < sheetNum; sheetIndex++) {
				//第几个sheet页
				params.setStartSheetIndex(sheetIndex);
				ExcelImportResult<CustomerExcelModel> result = ExcelImportUtil.importExcelMore(file.getInputStream(),
						CustomerExcelModel.class, params);
				bodyList.add(result.getList());
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return bodyList;
	}


	private ImportReportExcelVM getImportReportExcelVM(List<List<String>> importHeadList,
													   List<List<CustomerExcelModel>> importBodyList) {
		ImportReportExcelVM excelVM = new ImportReportExcelVM();
		List<FinancialReportExcelVM> excelDataList = new ArrayList<>();
		BigDecimal bg10000 = new BigDecimal(10000);
		for (int i = 0; i < importHeadList.size(); i++) {
			FinancialReportExcelVM vm = new FinancialReportExcelVM();
			excelDataList.add(vm);
			setCustomerId(importHeadList.get(i), vm);
			List<CustomerExcelModel> excelModels = importBodyList.get(i);
			for (int j = 0; j < excelModels.size(); j++) {
				CustomerExcelModel excelModel = excelModels.get(j);
//				if (j == 0) {
//					setYear(vm, excelModel);
//				} else if (j == 2) {
//					setCurrencyFunds(bg10000, vm, excelModel);
//				}
			}
		}
		excelVM.setExcelDataList(excelDataList);
		return excelVM;
	}

	private void setCurrencyFunds(BigDecimal bg10000, FinancialReportExcelVM vm, ExcelModel excelModel) {
		String value1 = excelModel.getValue1();
		if (StringUtils.isEmpty(value1)) {
			// TODO 抛异常
		}
		try {
			BigDecimal currencyFunds = new BigDecimal(value1).multiply(bg10000);
			vm.setCurrencyFunds(currencyFunds);
		} catch (Exception e) {
			// TODO 抛异常
			System.out.println("货币资金格式输入有误");
		}
	}


	private void setYear(FinancialReportExcelVM vm, ExcelModel excelModel) {
		String value1 = excelModel.getValue1();
		if (StringUtils.isEmpty(value1)) {
			// TODO 抛异常
		}
		try {
			long year = Long.parseLong(value1);
			vm.setYear(year);
		} catch (Exception e) {
			// TODO 抛异常
			System.out.println("年份格式输入有误");
		}
	}

	private void setCustomerId(List<String> list, FinancialReportExcelVM vm) {
		for (int j = 0; j < list.size(); j++) {
			if (j == 1) {
				String customerName = list.get(j);
				// TODO 这里查询客户id,查不到报错
				System.out.println(customerName);
				if (StringUtils.isEmpty(customerName)) {
					// TODO 字段为空抛异常
				}
				vm.setCustomerId(1L);
			}
		}
	}
}
